package app1.rapidsquare.com.database;

import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.LinkedList;
import java.util.TimeZone;

import org.apache.http.entity.StringEntity;
import org.json.JSONObject;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import app1.rapidsquare.com.Utility.Utility;
import app1.rapidsquare.com.classes.Contacts;
import app1.rapidsquare.com.classes.User;

public class AgileContactsProvider extends AgileSyncHelper<Contacts> implements
        IAgileProvider<Contacts> {

    // TABLE

    public static final String TABLE_NAME = "Contacts";

    // COLUMNS

    /*public static final String ID = "Id";
    public static final String SENT_USER_ID = "SentUserId";
    public static final String CONFIRM_USER_ID = "ConfirmUserId";
    public static final String CONFIRM_DATE = "ConfirmDate";
    public static final String NOTE = "Note";
    public static final String NOTIFICATION_STATUS = "NotificationStatus";
    public static final String SENT_DATE = "SentDate";
    public static final String MODIFIED_DATE = "ModifiedDate";
    public static final String IS_DELETE = "IsDelete";*/

    public static final String ID = "Id";
    public static final String NOTE = "Note";


    public AgileContactsProvider(Context context) {
        super(context, Contacts.class);
    }

    public synchronized LinkedList<User> SelectContacts() {

        LinkedList<User> mContactList = new LinkedList<User>();
        try {

            SQLiteDatabase sqliteDataBase = getSqliteHelper().getReadableDatabase();
            if (sqliteDataBase.isOpen()) {

                String mQuery = " SELECT * FROM ["
                        + TABLE_NAME
                        + "] ";


                Cursor mCursor = sqliteDataBase.rawQuery(mQuery, null);

                if (mCursor.getCount() < 1) {
                    if (!mCursor.isClosed())
                        mCursor.close();

                    sqliteDataBase.close();

                    Close();
                    return mContactList;
                }

                mCursor.moveToFirst();

                do {

                    User contact = new User();

                    contact.Id = mCursor.getInt(mCursor.getColumnIndex(ID));
                    contact.Email = mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.EMAIL));
                    contact.FirstName = mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.FIRST_NAME));
                    contact.LastName = mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.LAST_NAME));
                    contact.Phone = mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.PHONE));
                    contact.Title = mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.TITLE));
                    contact.Company = mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.COMPANY));
                    contact.Site = mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.SITE));
                    contact.Info = mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.INFO));
                    contact.Code = mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.CODE));
                    contact.AdditionalInfo = mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.ADDITIONAL_INFO));
                    contact.City = mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.CITY));
                    contact.State = mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.STATE));
                    contact.Note = mCursor.getString(mCursor
                            .getColumnIndex(NOTE));
                    contact.ModifiedDate = Utility.getUTCDateFormat().parse(mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.MODIFIED_DATE)));
                    contact.FacebookId = mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.FACEBOOK_ID));
                    contact.GooglePlusId = mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.GOOGLE_PLUS_ID));
                    contact.LinkedInId = mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.LINKED_IN_ID));
                    contact.FacebookName = mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.FACEBOOK_NAME));
                    contact.GooglePlusName = mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.GOOGLE_PLUS_NAME));
                    contact.LinkedInName = mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.LINKED_IN_NAME));

                    if (contact.Id != getUserId())
                        mContactList.add(contact);

                } while (mCursor.moveToNext());

                if (!mCursor.isClosed())
                    mCursor.close();

                sqliteDataBase.close();
            }
            Close();
        } catch (Exception ex) {
            Utility.Log("AgileContactProvider -> SelectContact ", ex);
        }
        return mContactList;
    }

    @Override
    public synchronized User SelectById(int id) {
        User userContact = null;
        try {
            SQLiteDatabase sqliteDataBase = getSqliteHelper().getReadableDatabase();
            if (sqliteDataBase.isOpen()) {

                String mQuery = " SELECT u.*,c.[Note] as Note FROM ["
                        + AgileUserProvider.TABLE_NAME
                        + "] u left join ["
                        + TABLE_NAME
                        + "] c on u.[Id]=c.[ConfirmUserId] and c.IsDelete=0 WHERE u.Id="
                        + id + " and u.IsDelete=0 ";


                Cursor mCursor = sqliteDataBase.rawQuery(mQuery, null);

                if (mCursor.getCount() < 1) {
                    if (!mCursor.isClosed())
                        mCursor.close();

                    sqliteDataBase.close();
                    Close();
                    return null;
                }

                mCursor.moveToFirst();

                do {
                    userContact = new User();
                    userContact.Id = mCursor.getInt(mCursor
                            .getColumnIndex("Id"));
                    userContact.FirstName = mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.FIRST_NAME));
                    userContact.LastName = mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.LAST_NAME));
                    userContact.Phone = mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.PHONE));
                    userContact.Title = mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.TITLE));
                    userContact.Company = mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.COMPANY));
                    userContact.Site = mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.SITE));
                    userContact.Info = mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.INFO));
                    userContact.Code = mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.CODE));
                    userContact.AdditionalInfo = mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.ADDITIONAL_INFO));
                    userContact.City = mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.CITY));
                    userContact.State = mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.STATE));
                    userContact.Note = mCursor.getString(mCursor
                            .getColumnIndex(NOTE));
                    userContact.FacebookId = mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.FACEBOOK_ID));
                    userContact.GooglePlusId = mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.GOOGLE_PLUS_ID));
                    userContact.LinkedInId = mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.LINKED_IN_ID));
                    userContact.FacebookName = mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.FACEBOOK_NAME));
                    userContact.GooglePlusName = mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.GOOGLE_PLUS_NAME));
                    userContact.LinkedInName = mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.LINKED_IN_NAME));

                    if (userContact.Id == getUserId())
                        userContact = new User();

                    break;

                } while (mCursor.moveToNext());

                if (!mCursor.isClosed())
                    mCursor.close();

                sqliteDataBase.close();
            }

            Close();
        } catch (Exception ex) {
            Utility.Log("AgileContactProvider -> SelectById ", ex);
        }


        return userContact;
    }

    public synchronized LinkedList<User> SelectByString(String text) {
        LinkedList<User> mContactList = new LinkedList<User>();
        try {
            SQLiteDatabase sqliteDataBase = getSqliteHelper().getReadableDatabase();
            if (sqliteDataBase.isOpen()) {

                /*String mQuery = " SELECT u.*,c.Id as contactId,c.Note as Note \n" +
                        "FROM " + AgileUserProvider.TABLE_NAME + " u inner join \n" +
                        "(SELECT ConfirmUserId as Id,Note as Note FROM " + TABLE_NAME + " WHERE SentUserId=" + getUserId() + " and NotificationStatus=1 and IsDelete=0) c on u.Id=c.Id " +
                        "WHERE u.IsDelete = 0 and " +
                        "u.[FirstName] like '%" + text + "%' or u.[LastName] like '%" + text + "%' or u.[Phone] like '%" + text + "%' or u.[Title] like '%" + text + "%' " +
                        "or u.[Company] like '%" + text + "%' or u.[Site] like '%" + text + "%' or u.[Info] like '%" + text + "%' " +
                        "or u.[FacebookName] like '%" + text + "%' " +
                        "or u.[LinkedInName] like '%" + text + "%' or u.[GooglePlusName] like '%" + text + "%' or [Note] like '%" + text + "%' ";*/

                String mQuery = " SELECT * FROM " + TABLE_NAME + " WHERE " +
                        "[FirstName] like '%" + text + "%' or [LastName] like '%" + text + "%' or [Phone] like '%" + text + "%' or [Title] like '%" + text + "%' " +
                        "or [Company] like '%" + text + "%' or [Site] like '%" + text + "%' or [Info] like '%" + text + "%' " +
                        "or [FacebookName] like '%" + text + "%' " +
                        "or [LinkedInName] like '%" + text + "%' or [GooglePlusName] like '%" + text + "%' or [Note] like '%" + text + "%' ";


                Cursor mCursor = sqliteDataBase.rawQuery(mQuery, null);

                if (mCursor.getCount() < 1) {
                    if (!mCursor.isClosed())
                        mCursor.close();

                    sqliteDataBase.close();

                    Close();
                    return mContactList;
                }

                mCursor.moveToFirst();

                do {

                    User contact = new User();

                    contact.Id = mCursor.getInt(mCursor.getColumnIndex(ID));
                    contact.FirstName = mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.FIRST_NAME));
                    contact.LastName = mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.LAST_NAME));
                    contact.Phone = mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.PHONE));
                    contact.Title = mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.TITLE));
                    contact.Company = mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.COMPANY));
                    contact.Site = mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.SITE));
                    contact.Info = mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.INFO));
                    contact.Code = mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.CODE));
                    contact.AdditionalInfo = mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.ADDITIONAL_INFO));
                    contact.City = mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.CITY));
                    contact.State = mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.STATE));
                    contact.Note = mCursor.getString(mCursor
                            .getColumnIndex(NOTE));
                    contact.FacebookId = mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.FACEBOOK_ID));
                    contact.GooglePlusId = mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.GOOGLE_PLUS_ID));
                    contact.LinkedInId = mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.LINKED_IN_ID));
                    contact.FacebookName = mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.FACEBOOK_NAME));
                    contact.GooglePlusName = mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.GOOGLE_PLUS_NAME));
                    contact.LinkedInName = mCursor.getString(mCursor
                            .getColumnIndex(AgileUserProvider.LINKED_IN_NAME));

                    if (contact.Id != getUserId())
                        mContactList.add(contact);

                } while (mCursor.moveToNext());

                if (!mCursor.isClosed())
                    mCursor.close();

                sqliteDataBase.close();
            }
            Close();
        } catch (Exception ex) {
            Utility.Log("AgileContactProvider -> SelectContact ", ex);
        }
        return mContactList;
    }

    @Override
    public synchronized void Update() {
       /* if (!Utility.isNullOrEmpty(getUserToken())) {
            try {
                SQLiteDatabase sqliteDataBase = getSqliteHelper().getReadableDatabase();
                if (sqliteDataBase.isOpen()) {

                    String lastModifiedDate = "2012-10-08 06:43:05";


                    Cursor c = sqliteDataBase.query(TABLE_NAME, new String[]{
                                    ID, MODIFIED_DATE}, null, null, null, null,
                            MODIFIED_DATE + " desc");

                    if (c.getCount() > 0) {

                        c.moveToFirst();

                        lastModifiedDate = c.getString(
                                c.getColumnIndex(MODIFIED_DATE)).trim();
                    }

                    if (!c.isClosed())
                        c.close();

                    sqliteDataBase.close();

                    setURL(Utility.CONTACTS_SYNC_URL);

                    JSONObject postObject = new JSONObject();
                    postObject.accumulate("UserToken", getUserToken());
                    postObject.accumulate("ModifiedDate", lastModifiedDate);

                    this.httpEntity = new StringEntity(postObject.toString());

                    RunSync();
                }
                Close();
            } catch (Exception e) {
                Utility.Log("AgileContactProvider - > Update ", e);
            }
        }*/
    }

    @Override
    public synchronized void Close() {
        if (getSqliteHelper() != null)
            getSqliteHelper().close();
    }

    @Override
    public synchronized void Delete() {

        try {
            SQLiteDatabase sqliteDataBase = getSqliteHelper().getWritableDatabase();
            if (sqliteDataBase.isOpen()) {
                sqliteDataBase.delete(TABLE_NAME, "IsDelete=1", null);
                sqliteDataBase.close();
            }

            Close();
        } catch (Exception e) {
            Utility.Log("AgileContactsProviser - > DELETE ", e);
        }
    }

    public synchronized void UpdateContact(LinkedList<User> contactList) {
        try {
            SQLiteDatabase sqliteDataBase = getSqliteHelper().getWritableDatabase();

            if (sqliteDataBase.isOpen()) {

                int id = 0;

                sqliteDataBase.delete(TABLE_NAME, null, null);

                for (User user : contactList) {

                    ContentValues values = new ContentValues();
                    values.put(ID, ++id);
                    values.put(AgileUserProvider.EMAIL, user.Email);
                    values.put(AgileUserProvider.FIRST_NAME, user.FirstName);
                    values.put(AgileUserProvider.LAST_NAME, user.LastName);
                    values.put(AgileUserProvider.PHONE, user.Phone);
                    values.put(AgileUserProvider.TITLE, user.Title);
                    values.put(AgileUserProvider.COMPANY, user.Company);
                    values.put(AgileUserProvider.SITE, user.Site);
                    values.put(AgileUserProvider.INFO, user.Info);
                    values.put(AgileUserProvider.CODE, user.Code);
                    values.put(AgileUserProvider.ADDITIONAL_INFO, user.AdditionalInfo);
                    if (user.ModifiedDate != null)
                        values.put(AgileUserProvider.MODIFIED_DATE, Utility.getUTCDateFormat().format(user.ModifiedDate));
                    else
                        values.put(AgileUserProvider.MODIFIED_DATE, Utility.getUTCDateFormat().format(Calendar.getInstance(TimeZone.getTimeZone("UTC")).getTime()));
                    values.put(AgileUserProvider.CITY, user.City);
                    values.put(AgileUserProvider.STATE, user.State);
                    values.put(AgileUserProvider.FACEBOOK_ID, user.FacebookId);
                    values.put(AgileUserProvider.GOOGLE_PLUS_ID, user.GooglePlusId);
                    values.put(AgileUserProvider.LINKED_IN_ID, user.LinkedInId);
                    values.put(AgileUserProvider.FACEBOOK_NAME, user.FacebookName);
                    values.put(AgileUserProvider.GOOGLE_PLUS_NAME, user.GooglePlusName);
                    values.put(AgileUserProvider.LINKED_IN_NAME, user.LinkedInName);
                    values.put(NOTE, user.Note);

                    long rowId = -1;

                    Cursor c = sqliteDataBase.query(TABLE_NAME, null, AgileUserProvider.CODE + "="
                            + user.Code, null, null, null, null);

                    if (c.getCount() > 0) {

                        rowId = sqliteDataBase.updateWithOnConflict(TABLE_NAME,
                                values, AgileUserProvider.CODE + "=" + user.Code + "", null, 5);

                    } else {

                        rowId = sqliteDataBase.insertWithOnConflict(TABLE_NAME,
                                null, values, 5);
                    }

                    if (!c.isClosed())
                        c.close();

                    if (rowId < 0) {
                        Utility.Log("AgileContactsProviser - > is not inserted or updated ", new Exception());
                    }
                }

                sqliteDataBase.close();
            }

            Close();

        } catch (Exception e) {
            Utility.Log("AgileContactsProviser - > UpdateContact", e);
        }
    }

    public synchronized void UpdateData(ArrayList<Contacts> contactList) {

        /*try {
            SQLiteDatabase sqliteDataBase = getSqliteHelper().getWritableDatabase();
            if (sqliteDataBase.isOpen()) {
                for (Contacts contact : contactList) {

                    ContentValues values = new ContentValues();
                    values.put(ID, contact.Id);
                    values.put(SENT_USER_ID, contact.SentUserId);
                    values.put(CONFIRM_USER_ID, contact.ConfirmUserId);
                    if (contact.ConfirmDate != null)
                        values.put(CONFIRM_DATE, Utility.getUTCDateFormat().format(contact.ConfirmDate));
                    else
                        values.put(CONFIRM_DATE, "");

                    values.put(NOTE, contact.Note);
                    values.put(NOTIFICATION_STATUS, contact.NotificationStatus);
                    if (contact.SentDate != null)
                        values.put(SENT_DATE, Utility.getUTCDateFormat().format(contact.SentDate));
                    else
                        values.put(SENT_DATE, "");
                    values.put(MODIFIED_DATE, Utility.getUTCDateFormat().format(contact.ModifiedDate));
                    values.put(IS_DELETE, contact.IsDelete ? 1 : 0);

                    long rowId = -1;

                    Cursor c = sqliteDataBase.query(TABLE_NAME, null, ID + "='"
                            + contact.Id + "'", null, null, null, null);

                    if (c.getCount() > 0) {

                        rowId = sqliteDataBase.updateWithOnConflict(TABLE_NAME,
                                values, ID + "='" + contact.Id + "'", null, 5);

                    } else {

                        rowId = sqliteDataBase.insertWithOnConflict(TABLE_NAME,
                                null, values, 5);
                    }

                    if (!c.isClosed())
                        c.close();

                    if (rowId < 0) {
                        Utility.Info("AgileContactsProviser ", " is not inserted or updated");
                    }
                }
                sqliteDataBase.close();
            }

            Close();

        } catch (Exception e) {
            Utility.Log("AgileContactsProvider -> UpdateData ", e);
        }*/


    }

    public synchronized void UpdateNote(User user) {
        try {
            SQLiteDatabase sqliteDataBase = getSqliteHelper().getWritableDatabase();

            if (sqliteDataBase.isOpen()) {
                ContentValues values = new ContentValues();
                values.put(ID, user.Id);
                values.put(AgileUserProvider.EMAIL, user.Email);
                values.put(AgileUserProvider.FIRST_NAME, user.FirstName);
                values.put(AgileUserProvider.LAST_NAME, user.LastName);
                values.put(AgileUserProvider.PHONE, user.Phone);
                values.put(AgileUserProvider.TITLE, user.Title);
                values.put(AgileUserProvider.COMPANY, user.Company);
                values.put(AgileUserProvider.SITE, user.Site);
                values.put(AgileUserProvider.INFO, user.Info);
                values.put(AgileUserProvider.CODE, user.Code);
                values.put(AgileUserProvider.ADDITIONAL_INFO, user.AdditionalInfo);
                if (user.ModifiedDate != null)
                    values.put(AgileUserProvider.MODIFIED_DATE, Utility.getUTCDateFormat().format(user.ModifiedDate));
                else
                    values.put(AgileUserProvider.MODIFIED_DATE, Utility.getUTCDateFormat().format(Calendar.getInstance(TimeZone.getTimeZone("UTC")).getTime()));
                values.put(AgileUserProvider.CITY, user.City);
                values.put(AgileUserProvider.STATE, user.State);
                values.put(AgileUserProvider.FACEBOOK_ID, user.FacebookId);
                values.put(AgileUserProvider.GOOGLE_PLUS_ID, user.GooglePlusId);
                values.put(AgileUserProvider.LINKED_IN_ID, user.LinkedInId);
                values.put(AgileUserProvider.FACEBOOK_NAME, user.FacebookName);
                values.put(AgileUserProvider.GOOGLE_PLUS_NAME, user.GooglePlusName);
                values.put(AgileUserProvider.LINKED_IN_NAME, user.LinkedInName);
                values.put(NOTE, user.Note);

                long rowId = -1;

                Cursor c = sqliteDataBase.query(TABLE_NAME, null, AgileUserProvider.CODE + "="
                        + user.Code, null, null, null, null);

                if (c.getCount() > 0) {

                    rowId = sqliteDataBase.updateWithOnConflict(TABLE_NAME,
                            values, AgileUserProvider.CODE + "=" + user.Code + "", null, 5);

                }

                if (!c.isClosed())
                    c.close();

                if (rowId < 0) {
                    Utility.Log("AgileContactsProviser - > is not inserted or updated ", new Exception());
                }

                sqliteDataBase.close();
            }

            Close();

        } catch (Exception e) {
            Utility.Log("AgileContactsProviser - > UpdateContact", e);
        }
    }
}
